﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Xml;

namespace DAO
{
    public class sqlDAO
    {
        public static string ReadConnection(string filename)
        {
            try
            {
                XmlDocument document = new XmlDocument();
                document.Load(filename);                
                XmlElement root = document.DocumentElement;
                string cnStr = root.InnerText;
                return cnStr;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        //public static string ConnectionString = ReadConnection("ConnectionString.xml");
        public static string ConnectionString = "Data Source = (local); Initial Catalog = QUANLYNHANSU; Integrated Security = SSPI;";
     
        #region ExecuteQuery
        public static DataTable ExecuteQuery(string spName,List <SqlParameter> sqlParams)
        {
            DataTable dt = new DataTable();
            try
            {
                //B2: Mo ket noi bang doi tuong ket noi
                SqlConnection cn = new SqlConnection(ConnectionString);
                cn.Open();
                try
                {
                    //B3: Tao chuoi sql thao tac voi CSDL:
                    SqlCommand cmd = cn.CreateCommand();
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = spName;
                    if (sqlParams != null)
                    {
                        foreach (SqlParameter param in sqlParams)
                        {
                            cmd.Parameters.Add(param);
                        }
                    }
                    //B4: Thuc thi chuoi sql
                    SqlDataAdapter da = new SqlDataAdapter(cmd);

                    da.Fill(dt);
                }
                catch (SqlException ex)
                {
                    throw ex;
                }
                finally
                {
                    //B5: Dong ket noi CSDL
                    cn.Close();
                }
            }
            catch(Exception ex)
            {
                //throw new Exception("Không thể kết nối cơ sở dữ liệu!");
                throw ex;
            }
            return dt;
        }
        public static DataTable ExecuteQuery(string spName)
        {
            return ExecuteQuery(spName, null);
        }
        #endregion

        #region ExecuteNoneQuery
        public static void ExecuteNoneQuery(string spName, List<SqlParameter> sqlParams)
        {
            //B2: mo ket noi bang doi tuong ket noi
            SqlConnection cn = new SqlConnection(ConnectionString);
            cn.Open();
            //B3: tao chuoi sql thao tac CSDL
            SqlCommand cmd = cn.CreateCommand();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = spName;
            if (sqlParams != null)
            {
                foreach (SqlParameter param in sqlParams)
                {
                    cmd.Parameters.Add(param);
                }
            }
            //B4: thuc thi chuoi sql
            cmd.ExecuteNonQuery();
            //B5: dong ket noi csdl
            cn.Close();
        }
        #endregion


    }
}
